1. Technical Field
The present invention relates to insertion of data into a database, such as SQL (structured query language) insertions in relational database management systems including multiple indexes.
2. Discussion of the Related Art
Data storage and maintenance operations within a database are particularly important for a variety of applications in which the need for relatively easy and quick access to large amounts of information is required. Structured Query Language (SQL) is a commonly used database computer language used for managing data in database systems such as relational database management systems (RDMS). Database systems such as RDMS utilize a database model in which data is stored in one or more tables with one or more indexes to access data in the table(s). Depending upon the amount of data in the database, multiple indexes can be useful to provide rapid random look ups and also efficient access and ordering of records. The indexes of a database are typically formatted as an index tree with nodes, including an initial root node, intermediate nodes and terminal nodes (also referred to as leaf pages).
Data records can be added to the table(s) of the database by performing an SQL INSERT function. When an SQL INSERT statement is executed to insert a data record into a database table, corresponding insert operations need to be performed on all the indexes defined on the same table. Typically, such insert operations are performed in a sequential manner. Thus, each insert operation of a record into an index has to be finished before the insert operation into the next index can be initiated. In scenarios in which the number of indexes defined on a table is large and the index pages needed for performing insert operations are not within a buffer pool, the processor performing the operations must wait for index page input/output (I/O) operations to finish for all indexes before the record insertion step can be completed. Therefore, the SQL INSERT operation for inserting a record into the database can encounter high response time due to the sequential wait times associated with processing each index page I/O operation.
One method for avoiding high wait times associated with the index page I/O operations involves performing parallel insertions on the indexes, which utilizes a separate processing task per index. While this method can effectively reduce the I/O wait times for accessing index pages, there is the potentially undesirable consequence of utilizing a large or excessive amount of central processing unit (CPU) capacity due to the multiple processing tasks being performed in parallel for the insertions and coordinating their completion.